package easybooking.db;

import easybooking.data.Airline;
import easybooking.data.Type;
import easybooking.data.Plane;
import easybooking.data.PlaneImpl;
import java.rmi.RemoteException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Plane</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.7249D751-5069-B2B7-283D-FBEE5728B00A]
// </editor-fold> 
public class PlaneMapper extends Mapper {

    /**
     * The class <code>PlaneMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see planeMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.FEE917C2-768A-7330-AAE2-6DB775CDE2DF]
    // </editor-fold> 
    private static PlaneMapper planeMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected PlaneMapper() {
    }

    /**
     * The static method can be called with
     * <code>PlaneMapper.planeMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>PlaneMapper</code> exists.

     * <b>Conclusion:</b> The <code>PlaneMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>PlaneMapper</code> object.
     * @see planeMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.ACF2F93B-A80B-DA35-ECC2-B686DBD47640]
    // </editor-fold> 
    public static PlaneMapper planeMapper() {
        if (planeMapper == null) {
            planeMapper = new PlaneMapper();
        }
        return planeMapper;
    }

    /**
     * Searching an airline with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Plane object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.CD43748E-8EA0-DE96-6922-51117E8B34F3]
    // </editor-fold> 
    public Plane findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT planeId, typeId, airlineId, name, isActive FROM Plane " +
                    "WHERE planeId=" + id);
            if (rs.next()) {
                try {
                    Plane p = new PlaneImpl();
                    p.setPlaneId(rs.getInt("planeId"));
                    p.setTypeId(rs.getInt("typeId"));
                    p.setAirlineId(rs.getInt("airlineId"));
                    p.setName(rs.getString("name"));
                    p.setIsActive(rs.getBoolean("isActive"));
                    return p;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active planes from the database.
     *
     * @return A vector of plane objects, which includes all active planes.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.6547BA91-AC6D-5D3A-320B-B6867C42822A]
    // </editor-fold>
    public Vector<Plane> findAll() {
        Connection con = DBConnection.connection();
        Vector<Plane> result = new Vector<Plane>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT planeId, typeId, airlineId, name, isActive FROM Plane " +
                    "WHERE isActive = true " +
                    "ORDER BY planeId ASC");
            while (rs.next()) {
                try {
                    Plane p = new PlaneImpl();
                    p.setPlaneId(rs.getInt("planeId"));
                    p.setTypeId(rs.getInt("typeId"));
                    p.setAirlineId(rs.getInt("airlineId"));
                    p.setName(rs.getString("name"));
                    p.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(p);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Plane</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param p the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.6722D200-90A8-81F3-8AB5-B7C3E78C47A8]
    // </editor-fold>
    public Plane insert(Plane p) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(planeId) AS maxPlaneId " +
                    "FROM Plane");
            if (rs.next()) {
                try {
                    p.setPlaneId(rs.getInt("maxPlaneId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Plane (planeId, typeId, airlineId, name, isActive) " +
                            "VALUES (" +
                            p.getPlaneId() + ",'" +
                            p.getTypeId() + "','" +
                            p.getAirlineId() + "','" +
                            p.getName() + "','" +
                            booleanToInt(p.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return p;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param p the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.B8537B53-4305-77F9-9387-B936918232C1]
    // </editor-fold>
    public Plane update(Plane p) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Plane " +
                    "SET typeId=\"" + p.getTypeId() + "\", " +
                    "airlineId=\"" + p.getAirlineId() + "\", " +
                    "name=\"" + p.getName() + "\", " +
                    "isActive=\"" + booleanToInt(p.getIsActive()) + "\" " +
                    "WHERE planeId=" + p.getPlaneId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        return p;
    }

    /**
     * Selecting all active panes from the database with a certain name,
     * which is given through a parameter.
     *
     * @param name the name or part of a name which should be searched for
     * @return A vector of plane objects, which includes all active planes
     * with a certain name.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Plane> findByName(String name) {
        Connection con = DBConnection.connection();
        Vector<Plane> result = new Vector<Plane>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT planeId, typeId, airlineId, name, isActive FROM Plane " +
                    "WHERE name LIKE '%" + name + "%' " +
                    "AND isActive = true " +
                    "ORDER BY planeId ASC");
            while (rs.next()) {
                try {
                    Plane p = new PlaneImpl();
                    p.setPlaneId(rs.getInt("planeId"));
                    p.setTypeId(rs.getInt("typeId"));
                    p.setAirlineId(rs.getInt("airlineId"));
                    p.setName(rs.getString("name"));
                    p.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(p);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active airlines from the database by a certain type,
     * which is given through a parameter.
     *
     * @param typeId the id of a type which should be searched for
     * @return A vector of plane objects, which includes all active planes
     * by a certain type.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.5CA1B751-4C5B-8B04-D6E1-4C5C0B162374]
    // </editor-fold>
    public Vector<Plane> findByType(int typeId) {
        Connection con = DBConnection.connection();
        Vector<Plane> result = new Vector<Plane>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT planeId, typeId, airlineId, name, isActive FROM Plane " +
                    "WHERE planeId=" + typeId +
                    " AND isActive = true " +
                    "ORDER BY planeId ASC");
            while (rs.next()) {
                try {
                    Plane p = new PlaneImpl();
                    p.setPlaneId(rs.getInt("planeId"));
                    p.setTypeId(rs.getInt("typeId"));
                    p.setAirlineId(rs.getInt("airlineId"));
                    p.setName(rs.getString("name"));
                    p.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(p);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active airlines from the database by a certain type,
     * which is given through a parameter.
     *
     * @param type the object of a type which should be searched for
     * @return A vector of plane objects, which includes all active planes
     * by a certain type. In case of an exception, an empty vector will be
     * returned.
     * @see findByType(int typeId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.6060F2C9-47CD-6466-4AAF-0E2F87494B29]
    // </editor-fold>
    public Vector<Plane> findByType(Type t) {
        try {
            return PlaneMapper.planeMapper().findByType(t.getTypeId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active planes from the database from a certain airline,
     * which is given through a parameter.
     *
     * @param airlineId the id of an airline which should be searched for
     * @return A vector of plane objects, which includes all active planes
     * from a certain airline.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.97586AE0-789B-1CD1-6872-00E04F23768B]
    // </editor-fold> 
    public Vector<Plane> findByAirline(int airlineId) {
        Connection con = DBConnection.connection();
        Vector<Plane> result = new Vector<Plane>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT planeId, typeId, airlineId, name, isActive FROM Plane " +
                    "WHERE airlineId=" + airlineId +
                    " AND isActive = true " +
                    "ORDER BY planeId ASC");
            while (rs.next()) {
                try {
                    Plane p = new PlaneImpl();
                    p.setPlaneId(rs.getInt("planeId"));
                    p.setTypeId(rs.getInt("typeId"));
                    p.setAirlineId(rs.getInt("airlineId"));
                    p.setName(rs.getString("name"));
                    p.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(p);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active planes from the database from a certain airline,
     * which is given through a parameter.
     *
     * @param a the object of an airline which should be searched for
     * @return A vector of plane objects, which includes all active planes
     * from a certain airline. In case of an exception, an empty vector will
     * be returned.
     * @see findByAirline(int airlineId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.42EDD214-A439-2972-49EB-E6B6A172DECE]
    // </editor-fold> 
    public Vector<Plane> findByAirline(Airline a) {
        try {
            return PlaneMapper.planeMapper().findByAirline(a.getAirlineId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }
}

